package idv.arthur.work;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import com.google.gson.Gson;

/**
 * Servlet implementation class RefMRItem
 */
public class RefMRItem extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public RefMRItem() {
        super();
    }

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		
		Widgets wid = new Widgets();
		String yymm = request.getParameter("yymm");
		String mrid = request.getParameter("mrid");
		int tpid = Integer.parseInt(request.getParameter("tpid"));
		String creator = request.getParameter("loginUser");
		Connection conn = wid.getConn(true);
		PreparedStatement ps = null;
		ResultSet rs = null;
		URL url = new URL( "http://10.80.71.9:8000/or/mr/xml/icssor%20"+yymm+".xml");
		String strSQL = "";
		String resultQuery = "";
		HttpURLConnection connection = (HttpURLConnection) url.openConnection();
		BufferedReader input = new BufferedReader( new InputStreamReader( (InputStream) connection.getContent() ) );
		MRBody[] mrBody = new MRBody[1];
		mrBody[0] = new MRBody();		
		
		
		try {
			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
			DocumentBuilder db = dbf.newDocumentBuilder();
			Document doc = db.parse((InputStream) connection.getContent());
			doc.getDocumentElement().normalize();

			NodeList nodeLst = doc.getElementsByTagName("mr");

			String nodeContent = "Default status has content";
			String nodeName = "";
			Element ele = null;
			NodeList elmntLst = null;

			for (int s = 0; s < nodeLst.getLength(); s++) {

				Node fstNode = nodeLst.item(s);

				if (fstNode.getNodeType() == Node.ELEMENT_NODE) {
					ele = (Element) fstNode;

					String tarMrid = fstNode.getAttributes().getNamedItem("id").getNodeValue();
					System.out.println(tarMrid);
					if (mrid.equalsIgnoreCase(tarMrid)) {
						
						mrBody[0].setMrid(mrid);
						nodeName = "module";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else { 
							nodeContent = elmntLst.item(0).getTextContent();
							mrBody[0].setModule(nodeContent);
						}

						nodeName = "mrdescription";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else {
							nodeContent = elmntLst.item(0).getTextContent();
							mrBody[0].setMrdescription(nodeContent);
						}
						
						nodeName = "tester";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else {
							nodeContent = elmntLst.item(0).getTextContent();
							if (nodeContent.equalsIgnoreCase("")) {
							} else {
								String[] tmp = nodeContent.split(" ");
								User[] userArray = null;
								if (tmp.length>0) {
									userArray = new User[tmp.length];
									for (int a=0;a<tmp.length;a++){
										userArray[a] = wid.checkUserId(tmp[a]);
									}	
								}
								mrBody[0].setTester(userArray);
							}
						}

						nodeName = "sourceid";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else {

							nodeContent = elmntLst.item(0).getTextContent();
							if (nodeContent.equalsIgnoreCase("")) {
							} else {
								mrBody[0].setSourceid(nodeContent);
							}
						}
						System.out.println(mrBody[0].getSourceid());
						nodeName = "propose";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else {
							nodeContent = elmntLst.item(0).getTextContent();
							if (nodeContent.equalsIgnoreCase("")) {
							} else {
								int start = 0;
								int len = nodeContent.length() / 3;
								String[] propose = new String[len];
								for (int i = 0; i < len; i++) {
									propose[i] = nodeContent.substring(start,
											start + 3);
									start = start + 3;
								} 
								
								String[] tmp = propose;
								User[] userArray = null;
								if (tmp.length>0) {
									userArray = new User[tmp.length];
									for (int a=0;a<tmp.length;a++){
										userArray[a] = wid.checkUserId(tmp[a]);
									}	
								}
								mrBody[0].setPropose(userArray);
							}
						}
						
						
						nodeName = "isMail";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else {
							nodeContent = elmntLst.item(0).getTextContent();
							if (nodeContent.equalsIgnoreCase("")) {
							} else {
								mrBody[0].setIsMail(nodeContent);
							}
						}
						
						nodeName = "onlinedate";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else {
							nodeContent = elmntLst.item(0).getTextContent();
							if (nodeContent.equalsIgnoreCase("")) {
							} else {
								mrBody[0].setOnlinedate(nodeContent);
							}
						}
						System.out.println(mrBody[0].getOnlinedate());
						nodeName = "createdate";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else {
							nodeContent = elmntLst.item(0).getTextContent();
							if (nodeContent.equalsIgnoreCase("")) {
							} else {
								mrBody[0].setCreatedate(nodeContent);
							}
						}
						System.out.println(mrBody[0].getCreatedate());
						nodeName = "mrno";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else {
							nodeContent = elmntLst.item(0).getTextContent();
							if (nodeContent.equalsIgnoreCase("")) {
							} else {
								mrBody[0].setMrno(nodeContent);
							}
						}

						nodeName = "process";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else {
							nodeContent = elmntLst.item(0).getTextContent();
							if (nodeContent.equalsIgnoreCase("")) {
							} else {
								mrBody[0].setProcess(nodeContent);
							}
						}

						nodeName = "desc";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else {
							nodeContent = elmntLst.item(0).getTextContent();
							if (nodeContent.equalsIgnoreCase("")) {
							} else {
								mrBody[0].setDesc(nodeContent);
							}
						}

						nodeName = "designpm";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else {
							nodeContent = elmntLst.item(0).getTextContent();
							if (nodeContent.equalsIgnoreCase("")) {
							} else {
								User[] userArray = new User[1];;
								userArray[0] = wid.checkUserId(nodeContent);
								mrBody[0].setDesignpm(userArray);
							}
						}

						
						nodeName = "designer";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else {
							nodeContent = elmntLst.item(0).getTextContent();
							if (nodeContent.equalsIgnoreCase("")) {
							} else {
								int start = 0;
								int len = nodeContent.length() / 3;
								String[] designer = new String[len];
								for (int i = 0; i < len; i++) {
									designer[i] = nodeContent.substring(start,
											start + 3);
									start = start + 3;
								}
								
								String[] tmp = designer;
								User[] userArray = null;
								if (tmp.length>0) {
									userArray = new User[tmp.length];
									for (int a=0;a<tmp.length;a++){
										userArray[a] = wid.checkUserId(tmp[a]);
									}	
								}
								mrBody[0].setDesigner(userArray);
							}
						}
						
						nodeName = "programmer";
						elmntLst = ele.getElementsByTagName(nodeName);
						if (elmntLst.item(0) == null) {
						} else {
							Programmer programmers[] = new Programmer[elmntLst.getLength()];
							for (int i=0;i<elmntLst.getLength();i++) {
								Programmer programmer = new Programmer();
								programmer.setName(elmntLst.item(i).getAttributes().getNamedItem("name").getNodeValue());
								Element element = (Element) elmntLst.item(i);
								programmer.setContent(element.getElementsByTagName("content").item(0).getTextContent());
								programmers[i] = programmer;
							}
							mrBody[0].setProgrammer(programmers);
						}

						nodeName = "reference";
						int size_ref = ele.getElementsByTagName(nodeName).getLength();
						elmntLst = ele.getElementsByTagName(nodeName);
						if (size_ref == 0) {
						} else {
							String[] reference = new String[size_ref];
							for (int i=0;i<size_ref;i++){
								nodeContent = elmntLst.item(i).getTextContent();
								if (nodeContent.equalsIgnoreCase("")) {
								} else {
									reference[i] = nodeContent;
									mrBody[0].setReference(reference);
								}							
							}
						}
						Require[] requeireLog = null; 
						mrBody[0].setRequire(requeireLog);
						System.out.println(mrBody[0].getMrdescription());
						/** 改變db內容 **/
						wid.deleteOwner(tpid, mrid, "designer", conn, ps, rs);
						wid.deleteOwner(tpid, mrid, "designpm", conn, ps, rs);
						wid.deleteOwner(tpid, mrid, "propose", conn, ps, rs);
						wid.deleteOwner(tpid, mrid, "reference", conn, ps, rs);
						wid.deleteOwner(tpid, mrid, "tester", conn, ps, rs);
						wid.deleteOwner(tpid, mrid, "programmer", conn, ps, rs);
						 
						for (int i=0;i<mrBody[0].getDesigner().length;i++) {
							wid.insertOwner(tpid, mrid, "designer", mrBody[0].getDesigner()[i].getUserID(), conn, ps, rs);
						}
						for (int i=0;i<mrBody[0].getDesignpm().length;i++) {
							wid.insertOwner(tpid, mrid, "designpm", mrBody[0].getDesignpm()[i].getUserID(), conn, ps, rs);
						}
						for (int i=0;i<mrBody[0].getPropose().length;i++) {
							wid.insertOwner(tpid, mrid, "propose", mrBody[0].getPropose()[i].getUserID(), conn, ps, rs);
						}
						for (int i=0;i<mrBody[0].getTester().length;i++) {
							wid.insertOwner(tpid, mrid, "tester", mrBody[0].getTester()[i].getUserID(), conn, ps, rs);
						}
						for (int i=0;i<mrBody[0].getReference().length;i++) {
							wid.insertOwner(tpid, mrid, "reference", mrBody[0].getReference()[i], conn, ps, rs);
						}
						System.out.println(mrBody[0].getMrid());
						//programmer
						strSQL = "INSERT INTO activiti.tms_mritem_programmer (tpid, mrid, userID,content) VALUES (?,?,?,?)";
						ps = conn.prepareStatement(strSQL);
						for (int i=0;i<mrBody[0].getProgrammer().length;i++) {
							ps.clearParameters();
							ps.setInt(1, tpid);
							ps.setString(2, mrid);
							ps.setString(3, mrBody[0].getProgrammer()[i].getName());
							ps.setString(4, mrBody[0].getProgrammer()[i].getContent());
							ps.execute();
						}
						
						
						strSQL = "UPDATE tms_tpitem SET tpid=?,mrid=?,module=?,mrdescription=?,onlinedate=?,sourceid=?,isMail=?,mrno=?,process=?,description=?,lastUpdatedDate=?,isTCsubmitAble=0,isTCsubmitted=0, isTCreviewed=0, isTCaccepted=null,isTRsubmitAble=0,isTRsubmitted=0,isTRreviewed=0,isTRaccepted=null WHERE tpid=? AND mrid=?";
						ps = conn.prepareStatement(strSQL);
						System.out.println(strSQL);
						ps.setInt(1, tpid);
						ps.setString(2, mrid);
						ps.setString(3, filter(mrBody[0].getModule()));
						ps.setString(4, filter(mrBody[0].getMrdescription()));
						ps.setString(5, filter(mrBody[0].getOnlinedate()));
						ps.setString(6, filter(mrBody[0].getSourceid()));
						ps.setString(7, filter(mrBody[0].getIsMail()));
						ps.setString(8, filter(mrBody[0].getMrno()));
						ps.setString(9, filter(mrBody[0].getProcess()));
						ps.setString(10, filter(mrBody[0].getDesc()));
						ps.setTimestamp(11, wid.currentTimeStamp());
						ps.setInt(12, tpid);
						ps.setString(13, mrid);
						ps.executeUpdate();
						
						
						/** 記錄重新匯入的動作 **/
						int ver = wid.getVer(tpid, mrid, conn, ps, rs);
						wid.keeplog(tpid, mrid, ver, 6, creator, conn, ps);
						
					}
				}
			}
		} catch (Exception e) {
			resultQuery = wid.keepErrorMes(conn, ps, e, "", true);
		} finally {
			try {
				if (rs!= null) {rs.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				resultQuery = e.toString()+"\n";
			}
		}
		input.close();
		resultQuery = new Gson().toJson(mrBody);
		System.out.println(resultQuery);
		response.setContentType("application/json");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(resultQuery);
	}
	
	public String filter(String str) {
		String res="";
		if (str != null){
			res=str;
		}
		return res;
	}
	
}
